package chen.web.util.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import chen.web.DataSourceProvider;

public class JdbcUtil {
	
	private static DataSource ds = DataSourceProvider.getDataSource();
	
	public static <T> T queryForObject(String sql, ResultSetHandler<T> handler, Object ...params){
		T result = null;
		try {
			Connection conn = ds.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			for(int i=0; params!=null && i<params.length; i++){
				ps.setObject(i+1, params[i]);
			}
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				result = handler.hander(rs);
			}
			if(rs.next()){
				throw new RuntimeException("结果集没有遍历完");
			}
			close(conn, ps, rs);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
		return result;
	}
	
	public static int[] batch(String sql, Object[] ...params){
		int[] result = null;
		try {
			Connection conn = ds.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			for(Object[] param : params){
				for(int i=0; param!=null && i<param.length; i++){
					ps.setObject(i+1, params[i]);	
				}
				ps.addBatch();
			}
			result = ps.executeBatch();
			close(conn, ps, null);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
		return result;
	}
	
	@SuppressWarnings("unchecked")
	public static <T> T update(String sql, T keyType, Object ...params){
		T result = null;
		try {
			Connection conn = ds.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			for(int i=0; params!=null && i<params.length; i++){
				ps.setObject(i+1, params[i]);
			}
			ps.executeUpdate();
			ResultSet rs = ps.getGeneratedKeys();
			if(rs.next()){
				result = (T)rs.getObject(1);
			}
			close(conn, ps, rs);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
		return result;
	}
	
	public static int update(String sql, Object ...params){
		int result = 0;
		try {
			Connection conn = ds.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			for(int i=0; params!=null && i<params.length; i++){
				ps.setObject(i+1, params[i]);
			}
			result = ps.executeUpdate();
			close(conn, ps, null);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
		return result;
	}
	
	public static int queryForInt(String sql, Object ...params){
		ResultSetHandler<Integer> handler = new ResultSetHandler<Integer>(){
			@Override
			public Integer hander(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				return rs.getInt(1);
			}
		};
		Integer result = queryForObject(sql, handler, params);
		return result == null ? 0 : result;
	}
	
	public static List<Map<String,?>> queryForMap(String sql, Object ...params){
		ResultSetHandler<List<Map<String,?>>> mapHandler = new ResultSetHandler<List<Map<String,?>>>(){
			@Override
			public List<Map<String, ?>> hander(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				List<Map<String,?>> result = new ArrayList<Map<String,?>>();
				ResultSetMetaData meta = rs.getMetaData();
				int count = meta.getColumnCount();
				do{
					Map<String,Object> map = new HashMap<String,Object>();
					for(int i=0; i<count; i++){
						String name = meta.getColumnName(i+1);
						Object value = rs.getObject(name);
						map.put(name, value);
					}
					result.add(map);
				}while(rs.next());
				return result;
			}
		};
		
		List<Map<String,?>> result = JdbcUtil.queryForObject(sql, mapHandler, params);
		return result == null ? new ArrayList<Map<String,?>>(0) : result;
	}
	
	public static <T> List<T> query(String sql, final ResultSetHandler<T> handler, Object ...params){
		ResultSetHandler<List<T>> listHandler = new ResultSetHandler<List<T>>(){
			@Override
			public List<T> hander(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				List<T> list = new ArrayList<T>();
				do{
					list.add(handler.hander(rs));
				}while(rs.next());
				return list;
			}
		};
		List<T> result = JdbcUtil.queryForObject(sql, listHandler, params);
		return result == null ? new ArrayList<T>(0) : result;
	}
	
	
	private static void close(Connection conn, Statement stmt, ResultSet rs){
		try {
			if(rs != null)
				rs.close();
		} catch (SQLException e) {
				// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}finally{
			try {
				if(stmt != null)
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					throw new RuntimeException(e);
				}finally{
					try {
						if(conn != null)
							conn.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						throw new RuntimeException(e);
					}
				}
		}	
	}
}
